package com.hualan.dao.impl;

import com.hualan.bean.PanghuCourierStation;
import com.hualan.dao.CourierStationListDao;
import com.hualan.utils.DruidUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CourierStationListDaoImpl implements CourierStationListDao {
    @Override
    public Long courierStationTotalCount() {
        Long total = null;
        // 查询数据库驿站总数
        String sql = "select count(courier_station_id) from panghu_courier_station";
        Connection connection = DruidUtil.getConn();
        QueryRunner queryRunner = new QueryRunner();
        // 从查询结果中提取单一的标量值 Long
        ScalarHandler<Long> scalarHandler = new ScalarHandler<>();
        try {
            total = queryRunner.query(connection, sql, scalarHandler);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return total;
    }

    @Override
    public List<PanghuCourierStation> courierStationList(String pageNum, String pageSize, String courierStationName, String communityName, String courierStationStatus, String startTime, String endTime) {
        // 判断查询条件
        String condition = " where 1 = 1";
        // 用列表接收有效参数
        List<String> params = new ArrayList<>();
        if (courierStationName != null && !"".equals(courierStationName)){
            condition += " and cs.courier_station_name like ?";
            params.add("%" + courierStationName + "%");
        }
        if (communityName != null && !"".equals(communityName)){
            condition += " and cy.community_name like ?";
            params.add("%" + communityName + "%");
        }
        if (startTime != null && !"".equals(startTime)){
            condition += " and cs.add_time >= ?";
            params.add(startTime);
        }
        if (endTime != null && !"".equals(endTime)){
            condition += " and cs.add_time <= ?";
            params.add(endTime);
        }
        condition += " limit " + pageNum + ", " + pageSize;
        String sql = "SELECT cs.courier_station_id, cs.courier_station_name, cs.courier_station_image, cs.courier_station_province, cs.courier_station_city, cs.courier_station_district, cs.courier_station_address, cs.courier_station_status, cs.add_time, cy.community_name as courier_station_community_name FROM panghu_courier_station cs LEFT JOIN panghu_community cy ON cs.courier_station_community_id = cy.community_id" + condition;
        System.out.println("完整的查询条件" + sql);

        System.out.println("查询前：" + params);
        // params.toArray(new Object[0])将列表转化成长度为0的object类型数组
        List<PanghuCourierStation> courierStationList = DruidUtil.query(sql, PanghuCourierStation.class, params.toArray());

        System.out.println("本次查询到的总条数：" + courierStationList.size());
        return courierStationList;

    }
}
